using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace axCommerce.Data
{
	[DataObject]
	[Serializable]
	public partial class Region
	{
		#region Constants

		private const string TableName = "[dbo].[Region]";

		#endregion

		#region Properties

		public int? RegionId { get; set; }
		public int? CountryId { get; set; }
		public string Name { get; set; }
		public bool? IsVisible { get; set; }

		#endregion

		#region Methods

		#region Mapping Methods

		protected void MapTo(DataSet ds)
		{
			if (ds == null)
				ds = new DataSet();

			if (ds.Tables[TableName] == null)
				ds.Tables.Add(TableName);

			ds.Tables[TableName].Columns.Add("RegionId", typeof (Int32));
			ds.Tables[TableName].Columns.Add("CountryId", typeof (Int32));
			ds.Tables[TableName].Columns.Add("Name", typeof (String));
			ds.Tables[TableName].Columns.Add("IsVisible", typeof (Boolean));

			DataRow dr = ds.Tables[TableName].NewRow();

			dr["RegionId"] = RegionId ?? (object) DBNull.Value;
			dr["CountryId"] = CountryId ?? (object) DBNull.Value;
			dr["Name"] = Name ?? (object) DBNull.Value;
			dr["IsVisible"] = IsVisible ?? (object) DBNull.Value;

			ds.Tables[TableName].Rows.Add(dr);
		}

		protected void MapFrom(DataRow dr)
		{
			RegionId = dr["RegionId"] != DBNull.Value ? Convert.ToInt32(dr["RegionId"]) : RegionId = null;
			CountryId = dr["CountryId"] != DBNull.Value ? Convert.ToInt32(dr["CountryId"]) : CountryId = null;
			Name = dr["Name"] != DBNull.Value ? Convert.ToString(dr["Name"]) : Name = null;
			IsVisible = dr["IsVisible"] != DBNull.Value ? Convert.ToBoolean(dr["IsVisible"]) : IsVisible = null;
		}

		private static List<Region> MapFrom(DataSet ds)
		{
			// Initialise Collection.
			List<Region> objects = new List<Region>();

			// Validation.
			if (ds == null)
				throw new ApplicationException("Cannot map to dataset null.");
			
			if (ds.Tables[TableName].Rows.Count == 0)
				return objects;

			if (ds.Tables[TableName] == null)
				throw new ApplicationException("Cannot find table [dbo].[Region] in DataSet.");

			if (ds.Tables[TableName].Rows.Count < 1)
				throw new ApplicationException("Table [dbo].[Region] is empty.");

			// Map DataSet to Instance.
			foreach (DataRow dr in ds.Tables[TableName].Rows)
			{
				var instance = new Region();
				instance.MapFrom(dr);
				objects.Add(instance);
			}

			objects.Sort();

			// Return collection.
			return objects;
		}

		#endregion

		#region CRUD Methods

		[DataObjectMethodAttribute(DataObjectMethodType.Select, false)]
		private static Region Get(Int32 regionId)
		{
			Region instance = new Region();

			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspRegion_SELECT";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, regionId))
			{
				using (DataSet ds = db.ExecuteDataSet(dbCommand))
				{
					if (ds == null || ds.Tables[0].Rows.Count == 0)
						throw new ApplicationException(String.Format("Could not get Region ID:{0} from Database.", regionId));
					// Return results.
					ds.Tables[0].TableName = TableName;

					instance.MapFrom(ds.Tables[0].Rows[0]);
				}
			}
			return instance;
		}

		#region INSERT

		private void Insert(Int32? regionId, Int32? countryId, String name, Boolean? isVisible, DbTransaction transaction = null)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspRegion_INSERT";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, regionId, countryId, name, isVisible))
			{
				if (transaction == null)
					db.ExecuteScalar(dbCommand);
				else
					db.ExecuteScalar(dbCommand, transaction);
			}
		}

		/// <summary>
		/// Insert current Region to database.
		/// </summary>
		/// <param name="transaction">optional SQL Transaction</param>
		private void Insert(DbTransaction transaction = null)
		{
			Insert(RegionId, CountryId, Name, IsVisible, transaction);
		}

		#endregion

		#region UPDATE

		private static void Update(Int32? regionId, Int32? countryId, String name, Boolean? isVisible,
		                          DbTransaction transaction = null)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspRegion_UPDATE";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand))
			{
				db.DiscoverParameters(dbCommand);
				dbCommand.Parameters["@regionId"].Value = regionId;
				dbCommand.Parameters["@countryId"].Value = countryId;
				dbCommand.Parameters["@name"].Value = name;
				dbCommand.Parameters["@isVisible"].Value = isVisible;

				if (transaction == null)
					db.ExecuteNonQuery(dbCommand);
				else
					db.ExecuteNonQuery(dbCommand, transaction);
			}
		}

		private static void Update(Region region)
		{
			region.Update();
		}

		private static void Update(Region region, DbTransaction transaction)
		{
			region.Update(transaction);
		}

		/// <summary>
		/// Updates changes to the database.
		/// </summary>
		/// <param name="transaction">optional SQL Transaction</param>
		private void Update(DbTransaction transaction = null)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspRegion_UPDATE";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand))
			{
				db.DiscoverParameters(dbCommand);
				dbCommand.Parameters["@regionId"].SourceColumn = "RegionId";
				dbCommand.Parameters["@countryId"].SourceColumn = "CountryId";
				dbCommand.Parameters["@name"].SourceColumn = "Name";
				dbCommand.Parameters["@isVisible"].SourceColumn = "IsVisible";

				using (DataSet ds = new DataSet())
				{
					MapTo(ds);
					ds.AcceptChanges();
					ds.Tables[0].Rows[0].SetModified();
					if (transaction == null)
						db.UpdateDataSet(ds, TableName, null, dbCommand, null, UpdateBehavior.Standard);
					else
						db.UpdateDataSet(ds, TableName, null, dbCommand, null, transaction);
				}
			}
		}

		#endregion

		#region DELETE

		[DataObjectMethodAttribute(DataObjectMethodType.Delete, false)]
		private static void Delete(Int32? regionId, DbTransaction transaction)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspRegion_DELETE";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, regionId))
			{
				if (transaction != null)
				{
					db.ExecuteNonQuery(dbCommand, transaction);
				}
				else
				{
					db.ExecuteNonQuery(dbCommand);
				}
			}
		}

		/// <summary>
		/// Delete current Region from database.
		/// </summary>
		/// <param name="transaction">optional SQL Transaction</param>
		private void Delete(DbTransaction transaction = null)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspRegion_DELETE";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, RegionId))
			{
				if (transaction != null)
				{
					db.ExecuteNonQuery(dbCommand, transaction);
				}
				else
				{
					db.ExecuteNonQuery(dbCommand);
				}
			}
		}

		#endregion

		#region SEARCH

		[DataObjectMethodAttribute(DataObjectMethodType.Select, false)]
		private static List<Region> Search(Int32? regionId = null, Int32? countryId = null, String name = null, Boolean? isVisible = null)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspRegion_SEARCH";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, regionId, countryId, name, isVisible))
			{
				using (DataSet ds = db.ExecuteDataSet(dbCommand))
				{
					ds.Tables[0].TableName = TableName;
					return MapFrom(ds);
				}
			}
		}


		private static List<Region> Search(Region searchObject)
		{
			return Search(searchObject.RegionId, searchObject.CountryId, searchObject.Name, searchObject.IsVisible);
		}

		#endregion

		#endregion

		#endregion
	}
}